
package com.feib.soeasy.dao.jdbc;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.feib.soeasy.dao.Soez5003Dao;
import com.feib.soeasy.model.WriteOffPayment;


/**
 * @title (#)Soez5003JdbcDao.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2011/3/14 create by Jimmy Liu<br>
 */
@Repository("soez5003Dao")
public class Soez5003JdbcDao extends JdbcDaoSupport implements Soez5003Dao {

    
    protected Logger logger = LoggerFactory.getLogger(this.getClass());
    @Resource(name = "dataSource")
    public void setMyDataSource(DataSource dataSource) {
        super.setDataSource(dataSource); 
    }
    
    /**
     * 超商統計資料
     * @param groupPk 帳單業者
     * @param channel 通路, D:7-11, E:OK, F:全家, G:萊爾富, NULL: 4家超商
     * @param qryDateType 資料期間基礎：1: 實際入帳日  2:繳款交易日。
     * @param qryDateFrom 資料期間起
     * @param qryDateTo 資料期間迄
     * @return 
     */
    public List<Map<String, Object>> getStatisticsForStore(Long groupPk, String channel, Integer qryDateType, Date qryDateFrom, Date qryDateTo)
    {
        StringBuffer sql = new StringBuffer();
        List<Object> args = new ArrayList<Object>();
        /*
        sql.append("select W.WRITE_OFF_PAYMENT_PK , P.PAYMENT_PK, P.PAYMENT_PK, U.FORMAT_DEF_PK");
        //sql.append(", P.BARCODE_STORES_1, P.BARCODE_STORES_1_LOAN");
        //sql.append(", SUBSTRING( P.BARCODE_STORES_1, 7, 9) AS COMMISSION_CODE");
        //sql.append(", SUBSTRING( P.BARCODE_STORES_1_LOAN, 7, 9) AS COMMISSION_CODE_LOAN");
        sql.append(", CASE W.AMOUNT WHEN P.TOTAL_LOANABLE_AMOUNT THEN SUBSTRING( P.BARCODE_STORES_1_LOAN, 7, 9) ELSE SUBSTRING( P.BARCODE_STORES_1, 7, 9) END AS COMMISSION_CODE");
        sql.append(", P.TOTAL_AMOUNT, P.TOTAL_LOANABLE_AMOUNT, W.AMOUNT");
        sql.append(", W.WRITE_OFF_DATE, W.CREATED_DATE");
        sql.append(" FORM WRITE_OFF_PAYMENT W INNER JOIN PAYMENT P ON W.WRITE_OFF_PAYMENT_PK = P.WRITE_OFF_PAYMENT_PK ");
        sql.append(" INNER JOIN UPLOAD_FILE U ON U.UPLOAD_FILE_PK = P.UPLOAD_FILE_PK");

        sql.append(" WHERE P.PAYMENT_STATUS = 5"); //已繳款
        sql.append(" AND P.GROUP_PK = ?"); // 帳單業者
        args.add(groupPk);
        sql.append(" AND CHANNEL = ?"); // 通路
        args.add(channel);
        // 資料期間
        if (new Integer(1).equals(qryType))
            sql.append(" AND W.CREATED_DATE between ? and ?"); // 實際入帳日
        else
            sql.append(" AND W.WRITE_OFF_DATE between ? and ?"); // 繳款交易日

        args.add(qryDateFrom);
        args.add(qryDateTo);
         */

        // 畫面上的代收手續費 = 超商成本 - 繳款者負擔 + 帳單業者負擔
        // 畫面上的 實際入帳金額 = 代收金額 - 畫面上的代收手續費 - 本行帳務處理費 
        /* 第二個版本 */
        sql.append("SELECT ").append("\r\n");
        sql.append("CHANNEL, CHANNEL_NAME, COMMISSION_CODE_PK, COMMISSION_CODE_NAME,GROUP_AMOUNT ").append("\r\n");
        sql.append(", COUNT(PAYMENT_PK) AS RECORD_COUNT ").append("\r\n");
        sql.append(", SUM(AMOUNT) AS AMOUNT ").append("\r\n"); // --代收金額
        // 代收手續費 = 帳單業者負擔
        sql.append(", SUM(GROUP_AMOUNT) AS COMMISSION_AMOUNT ").append("\r\n"); //代收手續費
        sql.append(", SUM(BANK_AMOUNT) AS BANK_AMOUNT ").append("\r\n");  //帳務處理費
        /*  電金部2011/05/09報表邏輯異動  
        1.手續費負擔別為"內含"時,"業者負擔"="超商成本"
        2.手續費負擔別為"外加"時,  "業者負擔"+  "繳費者負擔"  ="超商成本"
                    銀行端實際撥款金額(內含) =>  代收金額  -  "業者負擔"
                    銀行端實際撥款金額(外加) =>  代收金額   -  "業者負擔"
                    客戶端端實際撥款金額(內含) =>   代收金額-   代收手續費 ( "業者負擔"  )   -帳務處理費
                    客戶端實際撥款金額(外加) =>     代收金額-    代收手續費 ( "業者負擔"  ) -帳務處理費
*/
        //2011/05/13 jeff Gu  start   修正    客戶端端實際撥款金額(內含) =>   代收金額-   代收手續費 ( "業者負擔"  )   -帳務處理費
        sql.append(", SUM(AMOUNT - (GROUP_AMOUNT) - BANK_AMOUNT ) AS REAL_AMOUNT ").append("\r\n"); //實際入帳金額= 代收金額-代收手續費-帳務處理費 <==最後決議公式 20120121 jimmy
        //2011/05/13 jeff Gu  end 

        sql.append("FROM ").append("\r\n");
        sql.append("( ").append("\r\n");
        sql.append("SELECT TMP.* ").append("\r\n");
        sql.append(", C.COMMISSION_CODE_PK ").append("\r\n");
        sql.append(", C.COMMISSION_CODE_NAME ").append("\r\n");
        sql.append(", C.COMMISSION_TYPE ").append("\r\n");
        //sql.append(", ISNULL(C.COMMISSION_AMOUNT, 0 ) AS COMMISSION_AMOUNT ").append("\r\n"); //超商成本, 用不到了20120121 jimmy
        sql.append(", ISNULL(G.CHAINSTORE_AMOUNT, 0 ) AS BANK_AMOUNT ").append("\r\n"); //銀行帳務處理費, 20120121 jimmy
        //sql.append(", ISNULL(C.PAYER_AMOUNT, 0 ) AS PAYER_AMOUNT ").append("\r\n"); //繳款者負擔, 用不到了20120121 jimmy
        sql.append(", ISNULL(C.GROUP_AMOUNT, 0 ) AS GROUP_AMOUNT ").append("\r\n"); //代收手續費
        sql.append("FROM ").append("\r\n");
        sql.append("( ").append("\r\n");
        sql.append("select  P.GROUP_PK, W.WRITE_OFF_PAYMENT_PK ").append("\r\n");
        sql.append(", P.PAYMENT_PK ").append("\r\n");
        sql.append(", U.FORMAT_DEF_PK ").append("\r\n");
        sql.append(", W.AMOUNT ").append("\r\n");
        sql.append(", W.WRITE_OFF_DATE ").append("\r\n");
        sql.append(", W.CREATED_DATE ").append("\r\n");
        sql.append(", P.BARCODE_STORES_1 ").append("\r\n");
        //sql.append(", P.BARCODE_STORES_1_LOAN ");
        //sql.append(", SUBSTRING( P.BARCODE_STORES_1, 7, 9) AS COMMISSION_CODE ");
        //sql.append(", SUBSTRING( P.BARCODE_STORES_1_LOAN, 7, 9) AS COMMISSION_CODE_LOAN ");
        sql.append(", P.TOTAL_AMOUNT ").append("\r\n");
        sql.append(", P.TOTAL_LOANABLE_AMOUNT ").append("\r\n");
        sql.append(", W.CHANNEL ").append("\r\n");
        sql.append(", CT.CODE_NAME AS CHANNEL_NAME").append("\r\n");
        sql.append(", CASE  W.AMOUNT WHEN P.TOTAL_AMOUNT THEN SUBSTRING( P.BARCODE_STORES_1, 7, 9) ELSE  SUBSTRING( P.BARCODE_STORES_1_LOAN, 7, 9) END AS COMMISSION_CODE ").append("\r\n");
        sql.append(" from WRITE_OFF_PAYMENT w INNER JOIN PAYMENT P ON W.WRITE_OFF_PAYMENT_PK = P.WRITE_OFF_PAYMENT_PK ").append("\r\n");
        sql.append(" INNER JOIN UPLOAD_FILE U ON U.UPLOAD_FILE_PK = P.UPLOAD_FILE_PK ").append("\r\n");
        sql.append(" INNER JOIN CODE_TABLE CT ON CT.CODE_KIND='CHANEL' AND CT.CODE_VALUE = W.CHANNEL ").append("\r\n");
        sql.append(" where P.PAYMENT_STATUS = 5 ").append("\r\n"); //已繳款
        sql.append(" AND P.GROUP_PK = ? ").append("\r\n"); // 帳單業者
        args.add(groupPk);

        if (null != channel)
        {
            sql.append(" AND W.CHANNEL = ? ").append("\r\n"); // 通路
            args.add(channel);
        }
        else
        {
            sql.append(" AND W.CHANNEL in( ?, ?, ?, ?) ").append("\r\n"); // 通路
            args.add(WriteOffPayment.CHANNEL_7_11);
            args.add(WriteOffPayment.CHANNEL_FAMILY_MART);
            args.add(WriteOffPayment.CHANNEL_HILIFE);
            args.add(WriteOffPayment.CHANNEL_OK_MART);
        }

        // 資料期間 1: 銷帳日期  2:實際繳款日
        if (new Integer(1).equals(qryDateType))
            sql.append(" AND W.WRITE_OFF_DATE between ? and ? ").append("\r\n"); // 銷帳日期
        else
            sql.append(" AND W.TX_DATE between ? and ? ").append("\r\n"); // 實際繳款日

        args.add(qryDateFrom);
        args.add(qryDateTo);

        sql.append(") TMP ").append("\r\n");
        sql.append(" ").append("\r\n");
        sql.append("INNER JOIN FORMAT_DEF_COMMISSION F ON F.FORMAT_DEF_PK = TMP.FORMAT_DEF_PK ").append("\r\n");
        sql.append("INNER  JOIN COMMISSION_CODE_TABLE C  ON C.COMMISSION_CODE_PK = F.COMMISSION_CODE_PK AND C.COMMISSION_CODE = TMP.COMMISSION_CODE ").append("\r\n");
        sql.append("INNER JOIN APP_GROUP G ON G.GROUP_PK = TMP.GROUP_PK ").append("\r\n");
        
        sql.append(") D ").append("\r\n");
        sql.append("GROUP BY CHANNEL, CHANNEL_NAME, COMMISSION_CODE_PK, COMMISSION_CODE_NAME,GROUP_AMOUNT ").append("\r\n");
        sql.append("ORDER BY CHANNEL, COMMISSION_CODE_PK ").append("\r\n");


        logger.debug("5003getStatisticsForStore sql={};  args={}", sql, args);


        logger.debug("5003rs= {} ", this.getJdbcTemplate().queryForList(sql.toString(), args.toArray()).toString());


        return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());

    }
    
    /**
     * 非超商統計資料
     * @param groupPk
     * @param channel
     * @param qryType
     * @param qryDateFrom
     * @param qryDateTo
     * @return
     */
    public List<Map<String, Object>> getStatisticsForNotStore(Long groupPk, String channel, Integer qryType, Date qryDateFrom, Date qryDateTo)
    {
        StringBuffer sql = new StringBuffer();
        List<Object> args = new ArrayList<Object>();
        sql.append("SELECT ").append("\r\n");
        sql.append("CHANNEL, CHANNEL_NAME, COMMISSION_CODE_PK, COMMISSION_CODE_NAME,GROUP_AMOUNT ").append("\r\n");
        sql.append(", COUNT(PAYMENT_PK) AS RECORD_COUNT ").append("\r\n");
        sql.append(", SUM(AMOUNT) AS AMOUNT ").append("\r\n");
        //sql.append(", SUM(COMMISSION_AMOUNT) AS COMMISSION_AMOUNT ");
        // 非超商通路，不收 超商成本、帳單業者負擔、繳款者負擔，只收 本行帳務處理費
        sql.append(", SUM(GROUP_AMOUNT) AS COMMISSION_AMOUNT ").append("\r\n"); 
        sql.append(", SUM(BANK_AMOUNT) AS BANK_AMOUNT ").append("\r\n");
        //非超商沒代收
        sql.append(", SUM(AMOUNT - GROUP_AMOUNT - BANK_AMOUNT ) AS REAL_AMOUNT ").append("\r\n");
        sql.append("FROM ").append("\r\n");
        sql.append("( ").append("\r\n");
        sql.append("SELECT TMP.* ").append("\r\n");
        sql.append(", C.COMMISSION_CODE_PK ").append("\r\n");
        sql.append(", C.COMMISSION_CODE_NAME ").append("\r\n");
        sql.append(", C.COMMISSION_TYPE ").append("\r\n");
        //sql.append(", ISNULL(C.COMMISSION_AMOUNT, 0 ) AS COMMISSION_AMOUNT ");
        //sql.append(", ISNULL(C.BANK_AMOUNT, 0 ) AS BANK_AMOUNT ");
        //帳務處理費 20120121 jimmy
        sql.append(",CASE TMP.CHANNEL WHEN '1' THEN ISNULL(G.ATM_AMOUNT, 0 ) ").append("\r\n");
        sql.append(" WHEN '2' THEN ISNULL(G.ATM_AMOUNT, 0 ) ").append("\r\n");
        sql.append(" WHEN '3' THEN ISNULL(G.ATM_AMOUNT, 0 ) ").append("\r\n");
        sql.append(" WHEN '4' THEN ISNULL(G.ATM_AMOUNT, 0 ) ").append("\r\n"); 
        sql.append(" WHEN '0' THEN ISNULL(G.BANK_AMOUNT, 0 ) ").append("\r\n");
        sql.append(" WHEN '8' THEN ISNULL(G.REMITTANCE_AMOUNT, 0 ) ").append("\r\n");
        sql.append(" WHEN 'H' THEN ISNULL(G.POST_AMOUNT, 0 ) ").append("\r\n");
        
        /* 20120828 自行繳款之「帳務處理費」計算   */
        sql.append(" WHEN 'I' THEN ISNULL(G.OTHER_AMOUNT, 0 )  ").append("\r\n");
        sql.append(" WHEN 'Z' THEN ISNULL(G.SELF_AMOUNT, 0 ) ").append("\r\n"); 
        sql.append(" ELSE  0 ").append("\r\n"); 
        
        sql.append(" END AS BANK_AMOUNT ").append("\r\n");//帳務處理費
        //sql.append(", ISNULL(C.PAYER_AMOUNT, 0 ) AS PAYER_AMOUNT ");
        sql.append(",CASE TMP.CHANNEL WHEN 'H' THEN ").append("\r\n");
        sql.append("     CASE  WHEN AMOUNT <=0 THEN 0 ").append("\r\n");
        sql.append("           WHEN AMOUNT <=100 THEN ISNULL(G.POST_LEVEL1, 0 ) ").append("\r\n");
        sql.append("           WHEN AMOUNT <= 1000 THEN ISNULL(G.POST_LEVEL2 , 0 ) ").append("\r\n");
        sql.append("           ELSE ISNULL(G.POST_LEVEL3, 0 )").append("\r\n");
        sql.append("     END").append("\r\n");
        sql.append(" ELSE 0 ").append("\r\n"); //非超商通路，不收手續費
        sql.append(" END AS GROUP_AMOUNT ").append("\r\n");// 代收手續費
        //sql.append(", ISNULL(C.GROUP_AMOUNT, 0 ) AS GROUP_AMOUNT ").append("\r\n");
        sql.append("FROM ").append("\r\n");
        sql.append("( ").append("\r\n");
        sql.append("select P.GROUP_PK, W.WRITE_OFF_PAYMENT_PK ").append("\r\n");
        sql.append(", P.PAYMENT_PK ").append("\r\n");
        sql.append(", U.FORMAT_DEF_PK ").append("\r\n");
        sql.append(", W.AMOUNT ").append("\r\n");
        sql.append(", W.WRITE_OFF_DATE ").append("\r\n");
        sql.append(", W.CREATED_DATE ").append("\r\n");
        sql.append(", P.BARCODE_STORES_1 ").append("\r\n");
        sql.append(", P.TOTAL_AMOUNT ").append("\r\n");
        sql.append(", P.TOTAL_LOANABLE_AMOUNT ").append("\r\n");
        sql.append(", W.CHANNEL ").append("\r\n");
        sql.append(", CT.CODE_NAME AS CHANNEL_NAME").append("\r\n");
        sql.append(", SUBSTRING( P.BARCODE_STORES_1, 7, 9) AS COMMISSION_CODE ").append("\r\n");
        sql.append(" from WRITE_OFF_PAYMENT w INNER JOIN PAYMENT P ON W.WRITE_OFF_PAYMENT_PK = P.WRITE_OFF_PAYMENT_PK ").append("\r\n");
        sql.append(" INNER JOIN UPLOAD_FILE U ON U.UPLOAD_FILE_PK = P.UPLOAD_FILE_PK ").append("\r\n");
        sql.append(" INNER JOIN CODE_TABLE CT ON CT.CODE_KIND='CHANEL' AND CT.CODE_VALUE = W.CHANNEL ").append("\r\n");
        sql.append(" where P.PAYMENT_STATUS = 5 ").append("\r\n"); //已繳款
        sql.append(" AND P.GROUP_PK = ? ").append("\r\n"); // 帳單業者
        args.add(groupPk);
        
        if (null != channel)
        {
            sql.append(" AND W.CHANNEL = ? ").append("\r\n"); // 通路
            args.add(channel);
        }
        else
        {
            sql.append(" AND W.CHANNEL NOT IN ( ?, ?, ?, ?) ").append("\r\n"); // 通路
            args.add(WriteOffPayment.CHANNEL_7_11);
            args.add(WriteOffPayment.CHANNEL_FAMILY_MART);
            args.add(WriteOffPayment.CHANNEL_HILIFE);
            args.add(WriteOffPayment.CHANNEL_OK_MART);
        }
        
        // 資料期間 1: 銷帳日期  2:實際繳款日
        if (new Integer(1).equals(qryType))
            sql.append(" AND W.WRITE_OFF_DATE between ? and ? ").append("\r\n"); // 銷帳日期
        else
            sql.append(" AND W.TX_DATE between ? and ? ").append("\r\n"); // 實際繳款日

        args.add(qryDateFrom);
        args.add(qryDateTo);
        
        sql.append(") TMP ").append("\r\n");
        sql.append(" ");
        sql.append("INNER JOIN FORMAT_DEF_COMMISSION F ON F.FORMAT_DEF_PK = TMP.FORMAT_DEF_PK ").append("\r\n");
        sql.append("INNER JOIN COMMISSION_CODE_TABLE C  ON C.COMMISSION_CODE_PK = F.COMMISSION_CODE_PK AND C.COMMISSION_CODE = TMP.COMMISSION_CODE ").append("\r\n");
        sql.append("INNER JOIN APP_GROUP G ON G.GROUP_PK = TMP.GROUP_PK ").append("\r\n");
        sql.append(") D ").append("\r\n");
        sql.append("GROUP BY CHANNEL, CHANNEL_NAME, COMMISSION_CODE_PK, COMMISSION_CODE_NAME ,GROUP_AMOUNT ").append("\r\n");
        sql.append("ORDER BY CHANNEL, COMMISSION_CODE_PK ").append("\r\n");
        
         logger.debug("5003getStatisticsForNotStore sql={};  args={}", sql, args);
        
        
        logger.debug("5003rs= {} ", this.getJdbcTemplate().queryForList(sql.toString(), args.toArray()).toString());
        
        return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());
        
    }
    
    public List<Map<String, Object>> findCommissionCodesByFormatDefPk(Long formatDefPk)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("select distinct");
        sql.append(" COMMISSION_CODE_TABLE.COMMISSION_CODE_PK");
        sql.append(", COMMISSION_CODE_TABLE.COMMISSION_CODE");
        sql.append(", COMMISSION_CODE_TABLE.COMMISSION_CODE_NAME");
        sql.append(", COMMISSION_CODE_TABLE.AMOUNT_SCOPE_S");
        sql.append(", COMMISSION_CODE_TABLE.AMOUNT_SCOPE_E");
        sql.append(", COMMISSION_CODE_TABLE.COMMISSION_TYPE"); // 手續費類別 I:內含 , O:外加
        sql.append(", COMMISSION_CODE_TABLE.COMMISSION_AMOUNT");
        sql.append(", COMMISSION_CODE_TABLE.BANK_AMOUNT"); //本行帳務處理費
        sql.append(", COMMISSION_CODE_TABLE.PAYER_AMOUNT"); // 繳款者負擔(外加時有用)
        sql.append(", FORMAT_DEF_COMMISSION.FORMAT_DEF_PK");
        sql.append(" from COMMISSION_CODE_TABLE");
        sql.append(" inner join FORMAT_DEF_COMMISSION ");
        sql.append(" on COMMISSION_CODE_TABLE.COMMISSION_CODE_PK = FORMAT_DEF_COMMISSION.COMMISSION_CODE_PK");
        sql.append(" where FORMAT_DEF_COMMISSION.FORMAT_DEF_PK = ?");
        return this.getJdbcTemplate().queryForList(sql.toString(), new Object[]{formatDefPk});
    }
}
